<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;

use DB;

class Comcheckdetailself extends Model
{
    //
    
    protected $table = 'comcheckdetailself';

    public $timestamps = false;
    public function addAll(Array $data)
 	  {
      $rs = DB::table($this->getTable())->insert($data);
      return $rs;
  	}

  	public function getInconform($id)
  	{
  		$rs = DB::select("select checkentry.id as entryId,checkentry.entry,checkentry.proposal,detail.id as detailId,detail.pictures as pictures,detail.content as content,detail.negatives,detail.groupId from comcheckdetailself detail 
  						left join checkentry 
  						on checkentry.id = detail.entryId 
  						where detail.checkId = $id and detail.status = 2 ");
  		return $rs;
  	}

    public function getCheckDetail($companyId)
    {
      $rs = DB::select("
            SELECT cke.entry,cckd.entryId,cckd.groupId,cke.proposal,ckg.status,ckg.type,cckd.pictures,cckd.content,GROUP_CONCAT(ckng.content) as necontent,
            	group_concat(cka.title) as accordTitle 
            from comcheckdetailself cckd
            LEFT JOIN checkentry cke on cckd.entryId = cke.id
            LEFT JOIN checkgroup ckg on cckd.groupId = ckg.id
            LEFT JOIN comcheckself cck on cck.id = cckd.checkId
            LEFT JOIN checknegative ckng on FIND_IN_SET(ckng.id , cckd.negatives)
            LEFT JOIN checkaccord cka on cke.id = cka.entryId
            where cck.companyId = {$companyId} and cckd.status = 2 group by cckd.id 
        ");
      return $rs;
    }

    public function getAlidata()
    {
      $date = date('Y-m-d');
      $rs = DB::select("
          SELECT cckd.checkId,count(*) as count from comcheckdetailself cckd left join comcheckself cck on cckd.checkId = cck.id
          where cck.created > '{$date}' group by cckd.checkId
        ");
      return $rs;
    }


}
